package com.example.demo.web.dao;

import java.util.List;
import java.util.UUID;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.example.demo.web.Entity.OrganizationEntity;
import com.example.demo.web.Entity.RightRejectEntity;
import com.example.demo.web.Entity.RoleEntity;
import com.example.demo.web.Entity.RoleRightEntity;
import com.example.demo.web.util.XinLongUtil;


@Repository
public class RoleDAO {
	private Logger log = LogManager.getLogger(RoleDAO.class);
	private JdbcTemplate jdbcTemplate;
	private final String closeForeignKey = "SET @ORIG_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
	//打开mysql的外键检查功能,FOREIGN_KEY_CHECKS=1，表示检查外键
	private final String openForeignKey = "SET @ORIG_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1";
	

    @Autowired
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
    
    public int closeForeignKey(){
    	return jdbcTemplate.update(closeForeignKey);
    }
    
    public int openForeignKey(){
    	return jdbcTemplate.update(openForeignKey);
    }

    public int insertRole(RoleEntity roleEntity){    	
    	String sql = "INSERT INTO tbl_role (id,Createtime, Updatetime, Create_check_username, Update_check_username,Create_username,Update_username,Rolename,Subflags,Org_id)"
                + " VALUES (?, ?, ?, ?,?,?,?,?,?,?)";
    	try{
    		return jdbcTemplate.update(sql,roleEntity.getId(),roleEntity.getCreatetime(), roleEntity.getUpdatetime(),roleEntity.getCreate_check_username(),roleEntity.getUpdate_check_username(),
    				roleEntity.getCreate_username(), roleEntity.getUpdate_username(), roleEntity.getRolename(),roleEntity.getSubflags(),roleEntity.getOrg_id());
    	}catch(DuplicateKeyException de){
    		XinLongUtil.xinLongUtil.printlog(log,de.getMessage());
    		return -1;
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    	

    	
    };
    
    public int updateRole(RoleEntity roleEntity){
    	// update
		String sql = "UPDATE tbl_role SET Createtime=?, Updatetime=?, Create_check_username=?, Update_check_username=?, Create_username=?, Update_username=?, "
					+ "Rolename=?, Subflags=?,Org_id=? WHERE id=?";
		try{
			return jdbcTemplate.update(sql, roleEntity.getCreatetime(), roleEntity.getUpdatetime(),roleEntity.getCreate_check_username(),roleEntity.getUpdate_check_username(),
					roleEntity.getCreate_username(), roleEntity.getUpdate_username(), roleEntity.getRolename(),roleEntity.getSubflags(),roleEntity.getOrg_id(),roleEntity.getId());
		}catch(DuplicateKeyException de){
    		XinLongUtil.xinLongUtil.printlog(log,de.getMessage());
    		return -1;
    	}catch(Exception ex){
			XinLongUtil.xinLongUtil.printlog(log,ex.getMessage());
			return 0;
		}
		
    }
    
    public int deletebyid(String contactId) {
        String sql = "DELETE FROM tbl_role WHERE id=?";
        return jdbcTemplate.update(sql, contactId);
    }

    
    public RoleEntity findByRoleId(String Id){
    	String sql = "SELECT * FROM tbl_role WHERE id='" + Id + "'";
        
    	return jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(RoleEntity.class));
    };
    
    public RoleEntity findRolenameByUserId(String Id){
    	String sql = "SELECT role.* FROM tbl_role role,tbl_user_role ur WHERE ur.user_id='" + Id + "' and ur.role_id = role.id";
        try{
        	return jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(RoleEntity.class));
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return null;
        }
    	
    };
    
    public RoleEntity findByRoleName(String rolename) {
    	String sql = "SELECT * FROM tbl_role WHERE Rolename='" + rolename + "'";
    	try{
    		return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(RoleEntity.class));
    	}catch(EmptyResultDataAccessException er){
    		return null;
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }
    
    public List<RoleEntity> findPAll(List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "' and rl.Subflags = 1)");
    		}else{
    			sb.append(" or (rl.Org_id = '" + org.getId() + "' and rl.Subflags = 1)");
    		}
    		i++;
    	}
    	String sql = "SELECT rl.*,org.Name as orgname FROM tbl_role rl left join tbl_organization org on org.id = rl.Org_id where (rl.Org_id = '" + sb.toString() + "";
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(RoleEntity.class));
    }
    
    public List<RoleEntity> findsubAll(List<OrganizationEntity> orgs){    	
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or rl.Org_id = '" + org.getId() + "'");
    		}
    		i++;
    	}
    	String sql = "SELECT rl.*,org.Name as orgname FROM tbl_role rl left join tbl_organization org on org.id = rl.Org_id where rl.Org_id = '" + sb.toString();
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(RoleEntity.class));
    };
    
    //tbl_role_right
    public int insertRoleAndRight(RoleRightEntity rolerightEntity){    	
    	String sql = "INSERT INTO tbl_role_right (id,right_id,role_id)"
                + " VALUES (?, ?, ?)";
    	return jdbcTemplate.update(sql,rolerightEntity.getId(),rolerightEntity.getRight_id(), rolerightEntity.getRole_id());
    	

    	
    };
    
    public List<RoleRightEntity> getRoleRights(String roleid){
    	String sql = "SELECT right_id FROM tbl_role_right WHERE role_id='" + roleid + "'";
        try{
        	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(RoleRightEntity.class));
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return null;
        }
    }
    
    public int getRoleRightscount(String roleid){
    	String sql="select count(*) from tbl_role_right WHERE role_id = '" + roleid + "'";
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public int deleterightsbyid(String roleid) {
        String sql = "DELETE FROM tbl_role_right WHERE role_id=?";
        try{
        	jdbcTemplate.update(sql, roleid);
        	return 1;
        }catch(EmptyResultDataAccessException er){
        	return 1;
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return 0;
        }
        
    }
    
    /*=======================================tbl_rights=============================================================*/
    public String findRightname(String rightid){
    	String sql = "SELECT Name FROM tbl_rights WHERE id='" + rightid + "'";
        try{
        	return  jdbcTemplate.queryForObject(sql, String.class);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return "";
        }
    	
    }
    
    /*=======================================tbl_right_reject=======================================================*/
    public List<RightRejectEntity> findRejectdescByid(String rightid){
    	String sql = "SELECT * FROM tbl_right_reject WHERE Right_id = '" + rightid + "'";
    	try{
    		return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(RightRejectEntity.class));

        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return null;
        }
    }
    
    /*=======================================tbl_organization=======================================================*/
    public List<OrganizationEntity> findParentOrgs(String orgid) throws EmptyResultDataAccessException{    	
    	String sql = "select id,Orgcode,Name from ( select @r as _id, (select @r := Parentid from tbl_organization where id = _id) as parent_id, @l := @l + 1 as lv1 from ( select @r := '" + orgid + "' ,@l := 0) vars," +
    			"tbl_organization h where @r <> '') T1 join tbl_organization T2 on T1._id = T2.id where lv1 > 1 ORDER BY lv1;";
    	try{
    		XinLongUtil.xinLongUtil.printlog(log,sql);
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    	}catch(Exception e){
    		return null;
    	}
    	
    }
    
    public List<OrganizationEntity> findSubOrg(String parentid) throws EmptyResultDataAccessException{    	
    	String sql = "SELECT DATA.* FROM(SELECT @ids as _ids,( SELECT @ids := GROUP_CONCAT(id) FROM tbl_organization WHERE FIND_IN_SET(Parentid, @ids)) as cids," + 
			"@l := @l+1 as level FROM tbl_organization, (SELECT @ids :='" + parentid + "', @l := 0 ) b WHERE @ids IS NOT NULL" + 
			") id, tbl_organization DATA WHERE FIND_IN_SET(DATA.id, ID._ids)";
    	try{
    		XinLongUtil.xinLongUtil.printlog(log,sql);
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    	}catch(Exception e){
    		return null;
    	}
    	
    }
}
